package com.abc.recycling.mapper;

import com.abc.recycling.model.RecyclingStation;
import org.apache.ibatis.annotations.*;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import java.util.List;
import java.time.LocalDateTime;
import java.util.Map;

@Mapper
public interface RecyclingStationMapper {
    
    RecyclingStation findById(@Param("id") Long id);
    
    List<RecyclingStation> findAllActive();
    
    List<RecyclingStation> findAll();
    
    void insert(RecyclingStation station);
    
    void update(RecyclingStation station);
    
    @Delete("DELETE FROM recycling_station WHERE id = #{id}")
    void deleteById(@Param("id") Long id);
    
    @Select("SELECT * FROM recycling_station WHERE " +
            "(#{keyword} IS NULL OR name LIKE CONCAT('%', #{keyword}, '%') " +
            "OR address LIKE CONCAT('%', #{keyword}, '%') " +
            "OR contact_phone LIKE CONCAT('%', #{keyword}, '%')) " +
            "AND (#{area} IS NULL OR address LIKE CONCAT('%', #{area}, '%')) " +
            "ORDER BY created_at DESC " +
            "LIMIT #{pageable.offset}, #{pageable.pageSize}")
    Page<RecyclingStation> searchStations(
            @Param("keyword") String keyword,
            @Param("area") String area,
            @Param("pageable") Pageable pageable);
    
    @Select("SELECT *, " +
            "ST_Distance_Sphere(point(longitude, latitude), " +
            "point(#{longitude}, #{latitude})) as distance " +
            "FROM recycling_station " +
            "WHERE status = 1 " +
            "HAVING distance <= #{radius} * 1000 " +
            "ORDER BY distance")
    List<RecyclingStation> findNearbyStations(
            @Param("latitude") Double latitude,
            @Param("longitude") Double longitude,
            @Param("radius") Integer radius);

    @Select("SELECT COUNT(*) FROM recycling_station WHERE status = 1")
    Long countActiveStations();

    @Select("SELECT COUNT(*) FROM recycling_station")
    Long countTotalStations();

    @Select("SELECT s.*, COUNT(o.id) as order_count " +
            "FROM recycling_station s " +
            "LEFT JOIN recycling_order o ON s.id = o.station_id " +
            "WHERE s.status = 1 " +
            "GROUP BY s.id " +
            "ORDER BY order_count DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getTopStations(@Param("limit") int limit);

    @Select("SELECT region, COUNT(*) as count " +
            "FROM recycling_station " +
            "WHERE status = 1 " +
            "GROUP BY region " +
            "ORDER BY count DESC")
    List<Map<String, Object>> getStationDistributionByRegion();

    @Select("SELECT COUNT(*) FROM recycling_station " +
            "WHERE created_at BETWEEN #{startTime} AND #{endTime}")
    Long countStationsByDateRange(@Param("startTime") LocalDateTime startTime,
                                @Param("endTime") LocalDateTime endTime);

    @Select("SELECT s.*, COUNT(o.id) as order_count, " +
            "COALESCE(SUM(o.actual_weight), 0) as total_weight, " +
            "COALESCE(SUM(o.price), 0) as total_income " +
            "FROM recycling_station s " +
            "LEFT JOIN recycling_order o ON s.id = o.station_id " +
            "WHERE s.id = #{stationId} " +
            "AND o.created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY s.id")
    Map<String, Object> getStationStats(@Param("stationId") Long stationId,
                                      @Param("startTime") LocalDateTime startTime,
                                      @Param("endTime") LocalDateTime endTime);

    @Select("SELECT DATE_FORMAT(o.created_at, '%Y-%m-%d') as date, " +
            "COUNT(*) as order_count, " +
            "COALESCE(SUM(o.actual_weight), 0) as total_weight, " +
            "COALESCE(SUM(o.price), 0) as total_income " +
            "FROM recycling_station s " +
            "LEFT JOIN recycling_order o ON s.id = o.station_id " +
            "WHERE s.id = #{stationId} " +
            "AND o.created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY DATE_FORMAT(o.created_at, '%Y-%m-%d') " +
            "ORDER BY date DESC")
    List<Map<String, Object>> getStationDailyStats(@Param("stationId") Long stationId,
                                                  @Param("startTime") LocalDateTime startTime,
                                                  @Param("endTime") LocalDateTime endTime);

    @Select("SELECT item_type, COUNT(*) as count " +
            "FROM recycling_station s " +
            "JOIN recycling_order o ON s.id = o.station_id " +
            "WHERE s.id = #{stationId} " +
            "AND o.created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY item_type " +
            "ORDER BY count DESC")
    List<Map<String, Object>> getStationItemDistribution(@Param("stationId") Long stationId,
                                                        @Param("startTime") LocalDateTime startTime,
                                                        @Param("endTime") LocalDateTime endTime);
} 